/*
 * Delete all existing DB Object.
 * For rebuilding of the database; 
 */
DROP TABLE DBUSER CASCADE CONSTRAINTS;
DROP TABLE DBPIC CASCADE CONSTRAINTS; 
DROP TABLE DBTHUMB CASCADE CONSTRAINTS;
DROP TABLE DBEXIF CASCADE CONSTRAINTS; 
DROP TABLE DBEXIFVALUE CASCADE CONSTRAINTS; 
DROP TABLE DBKATEGORIE CASCADE CONSTRAINTS; 
DROP TABLE DBKEYWORD CASCADE CONSTRAINTS; 
DROP TABLE HABEN CASCADE CONSTRAINTS; 
DROP TABLE ZUORDNEN CASCADE CONSTRAINTS;

DROP SEQUENCE BNR_SEQ;
DROP SEQUENCE KEY_SEQ;
DROP SEQUENCE KNR_SEQ;


/*
 * Create Application user table.
 */
CREATE TABLE DBUSER
  (
    "USERNAME" VARCHAR2(50 BYTE),
    "PASSWORD" VARCHAR2(50 BYTE),
     CONSTRAINT PK_USER PRIMARY KEY (USERNAME)
  );



/*
 * Create database table and sequence.
 */
CREATE TABLE DBPIC
  (
    BNR  INTEGER,
    NAME VARCHAR(40),
    RATE INTEGER,
    BILD BLOB,
    CONSTRAINT PK_PIC PRIMARY KEY (BNR)
  );

CREATE TABLE DBTHUMB
  (
    BNR INTEGER,
    THUMB BLOB,
    THUMB_LOCK INTEGER,
    CONSTRAINT PK_THUMB PRIMARY KEY (BNR),
    CONSTRAINT FK_THUMB_PIC FOREIGN KEY (BNR) REFERENCES DBPIC(BNR) ON DELETE CASCADE
  );

/*
 * DBEXIF keeps the Photo's Metadata as STRING.
 */
CREATE TABLE DBEXIF
  (
    BNR             INTEGER,
    IMGHEIGHT       VARCHAR(50),
    IMGWIDTH        VARCHAR(50),
    EXPOSURETIME    VARCHAR(50),
    FNUMBER         VARCHAR(50),
    EXPOSUREPROGRAM VARCHAR(50),
    TIMEORIGINAL    VARCHAR(50),
    TIMEDIGITIZED   VARCHAR(50),
    COLORSPACE      VARCHAR(50),
    EXIFIMGWIDTH    VARCHAR(50),
    EXIFIMGHEIGHT   VARCHAR(50),
    EXPOSUREMODE    VARCHAR(50),
    MAKE            VARCHAR(50),
    MODEL           VARCHAR(50),
    ORIENTATION     VARCHAR(50),
    XRESOLUTION     VARCHAR(50),
    YRESOLUTION     VARCHAR(50),
    CONSTRAINT PK_EXIF PRIMARY KEY (BNR),
    CONSTRAINT FK_EXIF_PIC FOREIGN KEY (BNR) REFERENCES DBPIC (BNR) ON DELETE CASCADE
  );
  
/*
 * DBEXIFVALUE keeps the Photo's Metadata in what it should be.
 * Integer value as INTEGER,Time/Date as TIMESTAMP,FNumber as DECIMAL.
 * In order to search and compare the metadata value. 
 */
CREATE TABLE DBEXIFVALUE
  (
    BNR             INTEGER,
    IMGHEIGHT       INTEGER,
    IMGWIDTH        INTEGER,
    EXPOSURETIME    NUMBER(10,8),
    FNUMBER         NUMBER(5,1),
    EXPOSUREPROGRAM VARCHAR(50),
    TIMEORIGINAL  TIMESTAMP (8),
    TIMEDIGITIZED TIMESTAMP (8),
    COLORSPACE    VARCHAR(50),
    EXIFIMGWIDTH  INTEGER,
    EXIFIMAHEIGHT INTEGER,
    EXPOSUREMODE  VARCHAR(50),
    MAKE          VARCHAR(50),
    MODEL         VARCHAR(50),
    ORIENTATION   VARCHAR(50),
    XRESOLUTION   INTEGER,
    YRESOLUTION   INTEGER,
    CONSTRAINT PK_EXIFVALUE PRIMARY KEY (BNR),
    CONSTRAINT FK_EXIFVALUE_PIC FOREIGN KEY (BNR) REFERENCES DBPIC (BNR) ON DELETE CASCADE
  );

CREATE TABLE DBKATEGORIE
  (
    KTNR INTEGER,
    NAME VARCHAR(40),
    CONSTRAINT PK_KATEGORIE PRIMARY KEY (KTNR)
  );

CREATE TABLE DBKEYWORD
  (
    KWNR INTEGER,
    WORD VARCHAR(40),
    CONSTRAINT PK_KEYWORD PRIMARY KEY (KWNR)
  );

/*
 * DBPIC(BNR) <HABEN> DBKEYWORD(KWNR)
 */
CREATE TABLE HABEN
  (
    BNR  INTEGER,
    KWNR INTEGER,
    CONSTRAINT PK_HABEN PRIMARY KEY (BNR, KWNR),
    CONSTRAINT FK_HABEN_KEYWORD FOREIGN KEY (KWNR) REFERENCES DBKEYWORD (KWNR) ON DELETE CASCADE,
    CONSTRAINT FK_HABEN_PIC FOREIGN KEY (BNR) REFERENCES DBPIC (BNR) ON DELETE CASCADE
  );

/*
 * DBKATEGORIE(KTNR) <ZUORDNEN> DBPIC(BNR)
 */
CREATE TABLE ZUORDNEN
  (
    KTNR INTEGER,
    BNR  INTEGER,
    CONSTRAINT PK_ZUORDNEN PRIMARY KEY (KTNR, BNR),
    CONSTRAINT FK_ZUORDNEN_KATEGORIE FOREIGN KEY (KTNR) REFERENCES DBKATEGORIE (KTNR) ON DELETE CASCADE,
    CONSTRAINT FK_ZUORDNEN_PIC FOREIGN KEY (BNR) REFERENCES DBPIC (BNR) ON DELETE CASCADE
  );

CREATE SEQUENCE BNR_SEQ INCREMENT BY 1 START WITH 1;

CREATE SEQUENCE KEY_SEQ INCREMENT BY 1 START WITH 1;

CREATE SEQUENCE KNR_SEQ INCREMENT BY 1 START WITH 1;

INSERT INTO DBUSER VALUES ('benutzer','123');

SELECT KEY_SEQ.NEXTVAL FROM DUAL;
INSERT INTO DBKEYWORD VALUES (KEY_SEQ.NEXTVAL,'schön');
INSERT INTO DBKEYWORD VALUES (KEY_SEQ.NEXTVAL,'sauber');
INSERT INTO DBKEYWORD VALUES (KEY_SEQ.NEXTVAL,'groß');
INSERT INTO DBKEYWORD VALUES (KEY_SEQ.NEXTVAL,'klein');
INSERT INTO DBKEYWORD VALUES (KEY_SEQ.NEXTVAL,'fabrig');
INSERT INTO DBKEYWORD VALUES (KEY_SEQ.NEXTVAL,'schrecklich');


SELECT KNR_SEQ.NEXTVAL FROM DUAL;
INSERT INTO DBKATEGORIE VALUES (KNR_SEQ.NEXTVAL,'Nature');
INSERT INTO DBKATEGORIE VALUES (KNR_SEQ.NEXTVAL,'Tier');
INSERT INTO DBKATEGORIE VALUES (KNR_SEQ.NEXTVAL,'Mensch');
INSERT INTO DBKATEGORIE VALUES (KNR_SEQ.NEXTVAL,'Auto');
INSERT INTO DBKATEGORIE VALUES (KNR_SEQ.NEXTVAL,'Pflanze');
INSERT INTO DBKATEGORIE VALUES (KNR_SEQ.NEXTVAL,'Kinder');

COMMIT;